package com.alex.mda.jdbc;

import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;

import javax.xml.transform.Result;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;

import org.apache.commons.lang.exception.ExceptionUtils;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.DocumentSource;

import com.alex.mda.IConstants;
import com.alex.mda.jdbc.meta.Column;
import com.alex.mda.jdbc.meta.Table;

public class TableViewService {

	private static TableViewService me = new TableViewService();

	public static TableViewService getInstance() {
		return me;
	}

	private MetaService metaService = MetaService.getInstance();

	public String createQuerySQL4MySQL(String connName, String catalogName,
			String tableName) throws SQLException, IOException {
		Connection connection = metaService.getConn(connName);
		Map<String, Table> tableMap = metaService.getTables(connection,
				catalogName);
		Table tableObj = tableMap.get(tableName);
		Map<String, Column> colMap = tableObj.getColumns(connection);
		StringBuilder strb = new StringBuilder("Select ");
		for (String colName : colMap.keySet()) {
			strb.append(colName).append(",");
		}
		return new StringBuilder(strb.substring(0, strb.length() - 1))
				.append(" From ").append(tableName).append(" Limit 0,100")
				.toString();
	}

	public String showTable(String connName, String catalogName,
			String tableName) {
		try {
			Connection connection = metaService.getConn(connName);
			Map<String, Table> tableMap = metaService.getTables(connection,
					catalogName);
			Table tableObj = tableMap.get(tableName);
			Map<String, Column> colMap = tableObj.getColumns(connection);

			Document document = DocumentHelper.createDocument();
			Element tableElmt = document.addElement("table");
			for (String colName : colMap.keySet()) {
				String colRemarks = ((Column) colMap.get(colName)).getRemarks();
				if (colRemarks == null || "".equals(colRemarks)) {
					colRemarks = colName;
				} else {
					colRemarks = new StringBuilder(colName).append("(")
							.append(colRemarks).append(")").toString();
				}
				tableElmt.addElement("head").setText(colRemarks);
			}

			StringWriter sw = new StringWriter();
			Result rs = new StreamResult(sw);
			Transformer trans = TransformerFactory
					.newInstance()
					.newTransformer(
							new StreamSource(
									TableViewService.class
											.getResourceAsStream(IConstants.TABLE_VIEW_XSL)));
			trans.transform(new DocumentSource(document), rs);
			return sw.toString();
		} catch (Exception e) {
			return ExceptionUtils.getFullStackTrace(e);
		}
	}

	public String exeSQL(String connName, String catalogName, String sql) {
		boolean isQuery = true;

		if (sql == null) {
			return "SQL is null.";
		}
		if (sql.toLowerCase().startsWith("select ")
				&& sql.toLowerCase().indexOf(" limit ") == -1) {
			return "SQL Select must use Limit.";
		}
		if (!sql.toLowerCase().startsWith("select ")) {
			isQuery = false;
		}

		Statement statement = null;
		try {
			Connection conn = metaService.getConn(connName);
			conn.setCatalog(catalogName);
			statement = conn.createStatement();

			if (isQuery) {
				ResultSet result = statement.executeQuery(sql);
				ResultSetMetaData rsMetaData = result.getMetaData();

				Document document = DocumentHelper.createDocument();
				Element tableElmt = document.addElement("table");

				int colCount = rsMetaData.getColumnCount();
				for (int i = 1; i <= colCount; i++) {
					tableElmt.addElement("head").setText(
							rsMetaData.getColumnLabel(i));
				}
				while (result.next()) {
					Element bodyElmt = tableElmt.addElement("body");
					for (int i = 1; i <= colCount; i++) {
						String v;
						try {
							v = result.getString(i);
						} catch (Exception e) {
							v = "";
						}
						bodyElmt.addElement("column").setText(
								v == null ? "" : v);
					}
				}

				StringWriter sw = new StringWriter();
				Result rs = new StreamResult(sw);
				Transformer trans = TransformerFactory
						.newInstance()
						.newTransformer(
								new StreamSource(
										TableViewService.class
												.getResourceAsStream(IConstants.TABLE_VIEW_XSL)));
				trans.transform(new DocumentSource(document), rs);

				if (result != null) {
					result.close();
				}
				return sw.toString();
			} else {
				int rsc = statement.executeUpdate(sql);
				return rsc + " Updated.";
			}
		} catch (Exception e) {
			return ExceptionUtils.getFullStackTrace(e);
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					return ExceptionUtils.getFullStackTrace(e);
				}
			}
		}
	}

	public static void main(String[] args) throws Exception {
		String html = TableViewService.getInstance().exeSQL("localdb", "alex",
				"select * from employees");
		System.out.println(html);

		System.out.println(TableViewService.getInstance().createQuerySQL4MySQL(
				"localdb", "alex", "employees"));
	}
}
